package com.ctshk.rpc.localfun.mapper;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import com.ctshk.rpc.localfun.dto.CustomDto;
import com.ctshk.rpc.localfun.dto.es.LocalFunCandidateProductDto;
import com.ctshk.rpc.localfun.entity.LocalFunProductSaleStrategy;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;
import org.apache.ibatis.annotations.Update;

import java.time.LocalDate;
import java.time.LocalDateTime;
import java.util.List;

/**
 * @Description
 * @Author wansi <gaylen15527491065@yeah.net>
 * @Date 2021/3/24
 */
public interface LocalFunProductSaleStrategyMapper extends BaseMapper<LocalFunProductSaleStrategy> {

    /**
     * 清空策略
     * @param
     * @param
     */
    @Update("UPDATE t_local_fun_product_sale_strategy \n" +
            "SET is_deleted = 1,modified_id = #{modified_id}\n" +
            "WHERE product_id = #{product_id}")
    Integer clearByProductId(@Param("product_id") Long productId, @Param("modified_id")Long userId);

    @Select("<script>"+"select case when t1.id is not null then t1.id  else 0 end from t_local_fun_product_sale_strategy t1 left join t_local_fun_product_sale_strategy_vaild_date t2 on t1.id=t2.strategy_id where t1.product_id=#{product_id} and t2.time_begin &lt;=#{queryDate} and t2.time_end &gt;=#{queryDate} limit 1" + "</script>")
    Long getStrategyId(@Param("queryDate") LocalDate queryDate, @Param("product_id") Long productId);

    @Select("<script>SELECT\n" +
            " t4.is_limit isLimit,\n" +
            " t4.limit_num limitNum,\n" +
            " y2.total_stock solidStock,\n" +
            " t1.id productId,\n" +
            " t4.relation_id relationId,\n" +
            " t5.visable_type visableType,\n" +
            " t5.channel_type channelType\n" +
            " FROM\n" +
            " t_local_fun_product t1\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy y1 ON t1.id = y1.product_id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy_spec_stock y2 ON y1.id = y2.cost_id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy_vaild_date y3 ON y1.id = y3.cost_id\n" +
            " LEFT JOIN t_local_fun_product_sale_strategy t2 ON t1.id = t2.product_id\n" +
            " LEFT JOIN t_local_fun_product_sale_strategy_vaild_date t3 ON t2.id = t3.strategy_id\n" +
            " LEFT JOIN t_local_fun_product_channel_offline t5 ON t1.id = t5.product_id\n" +
            " LEFT JOIN t_local_fun_product_channel_offline_self_custom t4 ON t4.offline_id = t5.id\n" +
            " WHERE\n" +
            " DATE_FORMAT(y3.time_begin, '%Y-%m-%d') &lt;= #{now}\n" +
            " AND DATE_FORMAT(y3.time_end, '%Y-%m-%d') &gt;= #{now}\n" +
            " AND DATE_FORMAT(t3.time_begin, '%Y-%m-%d') &lt;= #{now}\n" +
            " AND DATE_FORMAT(t3.time_end, '%Y-%m-%d') &gt;= #{now}\n" +
            " AND t1.id = #{productId}\n" +
            " <if test=\"attrId1 != null and attrId1 != ''\">" +
            " AND y2.attr1_id = #{attrId1}\n" +
            " </if>" +
            " <if test=\"attrId2 != null and attrId2 != ''\">" +
            " AND y2.attr2_id = #{attrId2}\n" +
            " </if>" +
            " </script>")
    List<LocalFunCandidateProductDto> selectCandidatePrivliage(@Param("productId") Long productId, @Param("attrId1") Long attrId1, @Param("attrId2") Long attrId2, @Param("now") LocalDate now, @Param("relIds") List<String> relIds);

    @Select("<script>SELECT\n" +
            " t4.is_limit isLimit,\n" +
            " t4.limit_num limitNum,\n" +
            " y2.total_stock solidStock,\n" +
            " t1.id productId,\n" +
            " t4.agent_id relationId,\n" +
            " t5.visable_type visableType,\n" +
            " t5.channel_type channelType\n" +
            " FROM\n" +
            " t_local_fun_product t1\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy y1 ON t1.id = y1.product_id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy_spec_stock y2 ON y1.id = y2.cost_id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy_vaild_date y3 ON y1.id = y3.cost_id\n" +
            " LEFT JOIN t_local_fun_product_sale_strategy t2 ON t1.id = t2.product_id\n" +
            " LEFT JOIN t_local_fun_product_sale_strategy_vaild_date t3 ON t2.id = t3.strategy_id\n" +
            " LEFT JOIN t_local_fun_product_channel_offline t5 ON t1.id = t5.product_id\n" +
            " LEFT JOIN t_local_fun_product_channel_offline_agent_custom t4 ON t4.offline_id = t5.id\n" +
            " WHERE\n" +
            " DATE_FORMAT(y3.time_begin, '%Y-%m-%d') &lt;= #{now}\n" +
            " AND DATE_FORMAT(y3.time_end, '%Y-%m-%d') &gt;= #{now}\n" +
            " AND DATE_FORMAT(t3.time_begin, '%Y-%m-%d') &lt;= #{now}\n" +
            " AND DATE_FORMAT(t3.time_end, '%Y-%m-%d') &gt;= #{now}\n" +
            " AND t1.id = #{productId}\n" +
            " <if test=\"attrId1 != null and attrId1 != ''\">" +
            " AND y2.attr1_id = #{attrId1}\n" +
            " </if>" +
            " <if test=\"attrId2 != null and attrId2 != ''\">" +
            " AND y2.attr2_id = #{attrId2}\n" +
            " </if>" +
            "</script>")
    List<LocalFunCandidateProductDto> selectCandidateAgentPrivliage(@Param("productId") Long productId, @Param("attrId1") Long attrId1, @Param("attrId2") Long attrId2, @Param("now") LocalDate now, @Param("relId") Long relId);

    @Select("<script>SELECT\n" +
            " t4.is_limit isLimit,\n" +
            " t4.limit_num limitNum,\n" +
            " y2.total_stock solidStock,\n" +
            " t4.channel_id relationId,\n" +
            " t1.id productId\n" +
            " FROM\n" +
            " t_local_fun_product t1\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy y1 ON t1.id = y1.product_id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy_spec_stock y2 ON y1.id = y2.cost_id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy_vaild_date y3 ON y1.id = y3.cost_id\n" +
            " LEFT JOIN t_local_fun_product_sale_strategy t2 ON t1.id = t2.product_id\n" +
            " LEFT JOIN t_local_fun_product_sale_strategy_vaild_date t3 ON t2.id = t3.strategy_id\n" +
            " LEFT JOIN t_local_fun_product_channel_online_self_platform t4 ON t4.strategy_id = t2.id\n" +
            " WHERE\n" +
            " DATE_FORMAT(y3.time_begin, '%Y-%m-%d') &lt;= #{now}\n" +
            " AND DATE_FORMAT(y3.time_end, '%Y-%m-%d') &gt;= #{now}\n" +
            " AND DATE_FORMAT(t3.time_begin, '%Y-%m-%d') &lt;= #{now}\n" +
            " AND DATE_FORMAT(t3.time_end, '%Y-%m-%d') &gt;= #{now}\n" +
            " AND t1.id = #{productId}\n" +
            " AND t4.channel_id = #{relId}" +
            " <if test=\"attrId1 != null and attrId1 != ''\">" +
            " AND y2.attr1_id = #{attrId1}\n" +
            " </if>" +
            " <if test=\"attrId2 != null and attrId2 != ''\">" +
            " AND y2.attr2_id = #{attrId2}\n" +
            " </if>" +
            " GROUP BY\n" +
            " t1.id</script>")
    LocalFunCandidateProductDto selectCandidateOfficePrivliage(@Param("productId") Long productId, @Param("attrId1") Long attrId1, @Param("attrId2") Long attrId2, @Param("now") LocalDate now, @Param("relId") Long relId);

    @Select("<script>SELECT\n" +
            " t4.is_limit isLimit,\n" +
            " t4.limit_num limitNum,\n" +
            " y2.total_stock solidStock,\n" +
            " t4.platform_id relationId,\n" +
            " t1.id productId\n" +
            " FROM\n" +
            " t_local_fun_product t1\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy y1 ON t1.id = y1.product_id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy_spec_stock y2 ON y1.id = y2.cost_id\n" +
            " LEFT JOIN t_local_fun_product_cost_strategy_vaild_date y3 ON y1.id = y3.cost_id\n" +
            " LEFT JOIN t_local_fun_product_sale_strategy t2 ON t1.id = t2.product_id\n" +
            " LEFT JOIN t_local_fun_product_sale_strategy_vaild_date t3 ON t2.id = t3.strategy_id\n" +
            " LEFT JOIN t_local_fun_product_channel_online_third_platform t4 ON t4.strategy_id = t2.id\n" +
            " WHERE\n" +
            " DATE_FORMAT(y3.time_begin, '%Y-%m-%d') &lt;= #{now}\n" +
            " AND DATE_FORMAT(y3.time_end, '%Y-%m-%d') &gt;= #{now}\n" +
            " AND DATE_FORMAT(t3.time_begin, '%Y-%m-%d') &lt;= #{now}\n" +
            " AND DATE_FORMAT(t3.time_end, '%Y-%m-%d') &gt;= #{now}\n" +
            " AND t1.id = #{productId}\n" +
            " AND t4.platform_id = #{relId}" +
            " <if test=\"attrId1 != null and attrId1 != ''\">" +
            " AND y2.attr1_id = #{attrId1}\n" +
            " </if>" +
            " <if test=\"attrId2 != null and attrId2 != ''\">" +
            " AND y2.attr2_id = #{attrId2}\n" +
            " </if>" +
            " GROUP BY\n" +
            " t1.id</script>")
    LocalFunCandidateProductDto selectCandidateThirdPrivliage(@Param("productId") Long productId, @Param("attrId1") Long attrId1, @Param("attrId2") Long attrId2, @Param("now") LocalDate now, @Param("relId") Long relId);

    @Select("SELECT\n" +
            " t4.product_id,\n" +
            " t4.relation_id customId,\n" +
            " t4.`name` customName,\n" +
            " t4.check_type checkType,\n" +
            " t5.visable_type visableType\n" +
            " FROM\n" +
            " t_local_fun_product t1\n" +
            " LEFT JOIN t_local_fun_product_sale_strategy t2 ON t1.id = t2.product_id\n" +
            " LEFT JOIN t_local_fun_product_sale_strategy_vaild_date t3 ON t2.id = t3.strategy_id\n" +
            " LEFT JOIN t_local_fun_product_channel_offline t5 ON t5.product_id = t1.id\n" +
            " LEFT JOIN t_local_fun_product_channel_offline_self_custom t4 ON t4.offline_id = t5.id\n" +
            " WHERE\n" +
            " DATE_FORMAT(t3.time_begin, '%Y-%m-%d') <= #{now}\n" +
            " AND DATE_FORMAT(t3.time_end, '%Y-%m-%d') >= #{now}\n" +
            " AND t5.channel_type = 1\n" +
            " AND t1.id = #{productId}\n" +
            " group by t4.product_id,\n" +
            " t4.relation_id,\n" +
            " t4.`name`,\n" +
            " t4.check_type,\n" +
            " t5.visable_type")
    List<CustomDto> getCustomSelLine(@Param("productId") Long productId, @Param("now") LocalDate now);

    @Select("SELECT\n" +
            " t4.product_id,\n" +
            " t4.agent_id customId,\n" +
            " t4.`agent_name` customName,\n" +
            " t5.visable_type visableType\n" +
            " FROM\n" +
            " t_local_fun_product t1\n" +
            " LEFT JOIN t_local_fun_product_sale_strategy t2 ON t1.id = t2.product_id\n" +
            " LEFT JOIN t_local_fun_product_sale_strategy_vaild_date t3 ON t2.id = t3.strategy_id\n" +
            " LEFT JOIN t_local_fun_product_channel_offline t5 ON t5.product_id = t1.id\n" +
            " LEFT JOIN t_local_fun_product_channel_offline_agent_custom t4 ON t4.offline_id = t5.id\n" +
            " WHERE\n" +
            " DATE_FORMAT(t3.time_begin, '%Y-%m-%d') <= #{now}\n" +
            " AND DATE_FORMAT(t3.time_end, '%Y-%m-%d') >= #{now}\n" +
            " AND t5.channel_type = 1\n" +
            " AND t1.id = #{productId}\n" +
            " group by t4.product_id,\n" +
            " t4.agent_id,\n" +
            " t4.`agent_name`,\n" +
            " t5.visable_type")
    List<CustomDto> getAgentCustomSelLine(@Param("productId") Long productId, @Param("now") LocalDate now);
}
